with send_base as (--拿出基础数据,t-7 6点 - t 6点,且,中心-集散(网点)或者集散-网点
    select waybill_no        --运单编号
         , input_site        --扫描网点名称
         , scan_site_code    --扫描网点编号
         , network_type      --扫描网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
         , site_agent_code   --所属代理区code
         , site_agent_name   --所属代理区名称
         , package_code      --所属包号
         , scan_time         --扫描时间
         , next_station_code --下一站
         , next_network_type --下一站网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
         , nopack_flag       --1代表所属包号为空.0代表有包号
         , pack06_flag       --1代表所属包号B0-B6
         , network_type_flag --1是中心-(集散,网点)2是集散-(网点),0其他
         , date_time         --日期
         , rn                --去重标识1
    from (
             select waybill_no                                                                                              --运单编号
                  , input_site                                                                                              --扫描网点名称
                  , scan_site_code                                                                                          --扫描网点编号
                  , network_type                                                                                            --扫描网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
                  , site_agent_code                                                                                         --所属代理区code
                  , site_agent_name                                                                                         --所属代理区名称
                  , package_code                                                                                            --所属包号
                  , scan_time                                                                                               --扫描时间
                  , next_station_code                                                                                       --下一站
                  , next_network_type                                                                                       --下一站网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
                  , case
                        when package_code is null or package_code = '' then 1
                        else 0 end                                                                     as nopack_flag       --1代表所属包号为空.0代表有包号
                  , case
                        when substr(package_code, 1, 2) in ('B0', 'B1', 'B2', 'B3', 'B5', 'B6') then 1
                        else 0 end                                                                     as pack06_flag       --1代表所属包号B0-B6
                  , case
                        when network_type = 2 and next_network_type in (3, 4) then 1
                        when network_type = 3 and next_network_type = 4 then 2
                        else 0 end                                                                     as network_type_flag --1是中心-(集散,网点)2是集散-(网点),0其他
                  , case
                        when substr(scan_time, 12, 2) >= '06' then substr(scan_time, 1, 10)
                        when substr(scan_time, 12, 2) < '06' then date_add(substr(scan_time, 1, 10), -1)
                 end                                                                                   as date_time
                  , row_number() over (partition by waybill_no,scan_site_code order by scan_time desc) as rn
             from jms_dwd.dwd_tab_barscan_send_base_hi
             where substr(dt, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -6)
               and substr(dt, 1, 10) <= date_add('{{ execution_date | cst_ds }}', 1)
               and scan_time >= concat(date_add('{{ execution_date | cst_ds }}', -6), ' 06:00:00')
               and scan_time < concat(date_add('{{ execution_date | cst_ds }}', 1), ' 06:00:00')
               and substr(waybill_no, 1, 2) = 'JT'
         ) a
    where rn = 1
      and network_type_flag in (1, 2)
),
     waybill_incre as (--计算细长件,小件
         select waybill_no                             --运单号
              , first_code                             --一段码
              , second_code                            --二段码
              , third_code                             --三段码
              , package_length                         --包裹总长,单位厘米
              , package_wide                           --包裹总宽,单位厘米
              , package_high                           --包裹总高,单位厘米
              , goods_type_code                        --物品类型code
              , goods_type_name                        --物品类型名称
              , package_charge_weight                  --包裹计费重量,单位千克
              , case
                    when goods_type_code in ('bm000007', 'bm000008', 'bm000009') and
                         (package_length <= 30 and package_wide <= 30 and package_high <= 30) and
                         package_charge_weight <= 1 then 1
                    when goods_type_code not in ('bm000007', 'bm000008', 'bm000009') and
                         (package_length <= 30 and package_wide <= 30 and package_high <= 30) and
                         package_charge_weight <= 3 then 2
                    else 0 end as is_small_good_flag   --1,2代表小件,0代表非小件
              , case
                    when package_charge_weight <= 3 and
                         (package_length <= 70 and package_wide <= 70 and package_high <= 70) and
                         (package_length + package_wide + package_high) <= 90 and
                         (greatest(package_length, package_wide, package_high) /
                          (package_length + package_wide + package_high -
                           greatest(package_length, package_wide, package_high) -
                           least(package_length, package_wide, package_high))) >=
                         5
                        then 1
                    else 0 end as is_slender_good_flag --1代表细长件0代表非细长件
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_hi
         where substr(dt, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -30)
           and substr(dt, 1, 10) <= date_add('{{ execution_date | cst_ds }}', 1)
           and substr(waybill_no, 1, 2) = 'JT'
     ),
     bagging_base_pack as (--只有waybill_no字段为包号时,芯片号才会存在有值的情况
         select waybill_no     as package_code_flight -- 包号
              , flight_num                            --芯片号
              , scan_user                             --扫描雇员
              , scan_user_code                        --扫描雇员编号
              , scan_site      as pack_scan_site      --建包扫描网点名称
              , scan_site_code as pack_scan_site_code --建包扫描网点编码
              , network_type   as pack_network_type   --建包网点类型 1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
         from (
                  select waybill_no     -- 包号
                       , flight_num     --芯片号
                       , scan_user      --扫描雇员
                       , scan_user_code --扫描雇员编号
                       , scan_site      --建包扫描网点名称
                       , scan_site_code --建包扫描网点编码
                       , network_type   --建包网点类型 1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
                       , row_number() over (partition by waybill_no order by scan_time desc ) as rn
                  from jms_dwd.dwd_tab_barscan_bagging_base_hi a
                  where substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -30) and date_add('{{ execution_date | cst_ds }}', 1)
                    and waybill_no like 'B%'
              ) a
         where rn = 1
     ),
     bagging_base_wjbill as (--剔除文件票数
         select waybill_no --运单
              , max(good_type) as pack_good_type
              , max(scan_time) as pack_scan_time
         from jms_dwd.dwd_tab_barscan_bagging_base_hi a
         where substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -30) and date_add('{{ execution_date | cst_ds }}', 1)
           and waybill_no like 'JT%'
--            and a.good_type = 2
         group by waybill_no
     ),
     third_package as (
         select package_number      --包号
              , second_package_code --集包编码(二段码)
              , third_package_code  --集包编码(三段码)
         from (
                  select package_number      --包号
                       , second_package_code --集包编码(二段码)
                       , third_package_code  --集包编码(三段码)
                       , row_number() over (partition by package_number order by used_time desc ) as rn
                  from jms_dwd.dwd_third_package_list_base_hi
                  where substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -30) and date_add('{{ execution_date | cst_ds }}', 1)
              ) a
         where rn = 1
     ),
     vehicle_line as ( --通过始发和目的,剔除干线直发
         select start_code                    --始发网点编号
              , max(start_name) as start_name --始发网点名称
              , end_code                      --目的网点编号
              , max(end_name)   as end_name --目的网点名称
         from jms_dim.dim_tmsnew_vehicle_line
         group by start_code --始发网点编号
                , end_code   --目的网点编号
     ),
     join_table as (
         select sen.waybill_no                                                    --运单编号
              , sen.input_site                                                    --扫描网点名称
              , sen.scan_site_code                                                --扫描网点编号
              , sen.network_type                                                  --扫描网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
              , sen.site_agent_code                                               --所属代理区code
              , sen.site_agent_name                                               --所属代理区名称
              , sen.package_code                                                  --所属包号
              , sen.scan_time                                                     --扫描时间
              , sen.next_station_code                                             --下一站
              , sen.next_network_type                                             --下一站网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
              , sen.nopack_flag                                                   --1代表所属包号为空.0代表有包号
              , sen.pack06_flag                                                   --1代表所属包号B0-B6,0其他
              , sen.network_type_flag                                             --1是中心-(集散,网点),2是集散-(网点),0其他
              , sen.date_time                                                     --日期
              , sen.rn                                         as way_net_falg    --去重标识1,确定运单号在一个站点唯一
              , way.first_code                                                    --一段码
              , way.second_code                                                   --二段码
              , way.third_code                                                    --三段码
              , way.package_length                                                --包裹总长,单位厘米
              , way.package_wide                                                  --包裹总宽,单位厘米
              , way.package_high                                                  --包裹总高,单位厘米
              , way.goods_type_code                                               --物品类型code
              , way.goods_type_name                                               --物品类型名称
              , way.package_charge_weight                                         --包裹计费重量,单位千克
              , way.is_small_good_flag                                            --1,2代表小件,0代表非小件
              , way.is_slender_good_flag                                          --1代表细长件0代表非细长件
              , bpa.flight_num                                                    --芯片号
              , concat(bpa.scan_user, '-', bpa.scan_user_code) as scan_user       --扫描员
              , bpa.pack_scan_site                                                --建包扫描网点名称
              , bpa.pack_scan_site_code                                           --建包扫描网点编码
              , bpa.pack_network_type                                             --建包网点类型 1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
              , if(bpa.flight_num is not null, 1, 0)           as flight_flag     --1代表有芯片号,0代表没有
              , bwj.pack_good_type                                                --建表运单对应物品类型
              , bwj.pack_scan_time                                                --建包扫描时间
              , if(bwj.pack_good_type = 2, 1, 0)               as pack_good_flag  --1代表包件是文件,0代表包件是其他
              , thi.second_package_code                                           --包二段码
              , thi.third_package_code                                            --包三段码
              , if(thi.package_number is not null, 1, 0)       as third_pack_flag --1代表三段码包表有相应数据
              , veh.start_code                                                    --始发网点编号
              , veh.start_name                                                    --始发网点名称
              , veh.end_code                                                      --目的网点编号
              , veh.end_name                                                      --目的网点名称
              , if(veh.start_code is not null, 1, 0)           as gxzf_veh_flag   --1代表干线直发,0代表非
         from send_base sen
                  left join waybill_incre way
                            on sen.waybill_no = way.waybill_no --小件,细长件
                  left join bagging_base_pack bpa
                            on sen.package_code = bpa.package_code_flight --循环袋
                  left join bagging_base_wjbill bwj --建包运单为文件类型的
                            on sen.waybill_no = bwj.waybill_no
                  left join third_package thi --建包流向通过二段和三段码与运单的匹配
                            on sen.package_code = thi.package_number
                  left join vehicle_line veh
                            on sen.scan_site_code = veh.start_code
                                and sen.next_station_code = veh.end_code
     )
insert
overwrite
table
jms_dm.dm_tab_in_center_package_waybill_base_detail_dt
partition
(
dt
)
select waybill_no                            --运单编号
     , input_site                            --扫描网点名称
     , scan_site_code                        --扫描网点编号
     , network_type                          --扫描网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
     , site_agent_code                       --所属代理区code
     , site_agent_name                       --所属代理区名称
     , package_code                          --所属包号
     , scan_time                             --扫描时间
     , next_station_code                     --下一站
     , next_network_type                     --下一站网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
     , pack06_flag                           --1代表所属包号B0-B6,0其他
     , network_type_flag                     --1是中心-(集散,网点),2是集散-(网点),0其他
     , date_time                             --日期
     , nopack_flag                           --1代表所属包号为空.0代表有包号
     , way_net_falg                          --去重标识1,确定运单号在一个站点唯一
     , first_code                            --运单一段码
     , second_code                           --运单二段码
     , third_code                            --运单三段码
     , package_length                        --包裹总长,单位厘米
     , package_wide                          --包裹总宽,单位厘米
     , package_high                          --包裹总高,单位厘米
     , goods_type_code                       --物品类型code
     , goods_type_name                       --物品类型名称
     , package_charge_weight                 --包裹计费重量,单位千克
     , is_small_good_flag                    --1,2代表小件,0代表非小件
     , is_slender_good_flag                  --1代表细长件0代表非细长件
     , flight_num                            --芯片号
     , scan_user                             --扫描员
     , pack_scan_site                        --建包扫描网点名称
     , pack_scan_site_code                   --建包扫描网点编码
     , pack_network_type                     --建包网点类型 1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
     , flight_flag                           --1代表有芯片号,0代表没有
     , pack_good_type                        --建表运单对应物品类型
     , pack_scan_time                        --建包扫描时间
     , pack_good_flag                        --1代表包件是文件,0代表包件是其他
     , second_package_code                   --包二段码
     , third_package_code                    --包三段码
     , third_pack_flag                       --1代表三段码包表有相应数据
     , start_code                            --始发网点编号
     , start_name                            --始发网点名称
     , end_code                              --目的网点编号
     , end_name                              --目的网点名称
     , gxzf_veh_flag                         --1代表干线直发,0代表非
     , case
           when (is_small_good_flag in (1, 2) or is_slender_good_flag = 1) and pack_good_flag = 0 and
                gxzf_veh_flag = 0 and pack06_flag = 0 then 1
           else 0 end as need_way_flag       --应建包票运单标识 1是,0否
     , case
           when nopack_flag = 0 and
                (second_code <> second_package_code or !array_contains(split(third_package_code, ','), third_code))
               then 0
           else 1 end as error_pack_way_flag --错建包运单 1代表未错建包,0代表错建包
     , date_time         dt
from join_table

 distribute by pmod(hash(rand()), 100);
